@echo off

cd %~dp0

call ..\setConnectivity.cmd || echo Failed to call ..\setConnectivity.cmd && exit /b 1

IF "%STAGING_DATABASE_OS_FILEPATH%" == "" (
	sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -b -d master -i Database\database.sql -v STAGING_DATABASE_NAME="%STAGING_DATABASE_NAME%" || echo Failed to create database && exit /b 1
	goto createFilegroups
)

IF NOT EXIST %STAGING_DATABASE_OS_FILEPATH% (
  mkdir %STAGING_DATABASE_OS_FILEPATH% || echo Failed to create directory [%STAGING_DATABASE_OS_FILEPATH%] && exit /b 1
)

sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -b -d master -i Database\database_filepath.sql -v STAGING_DATABASE_NAME="%STAGING_DATABASE_NAME%" STAGING_DATABASE_OS_FILEPATH="%STAGING_DATABASE_OS_FILEPATH%" || echo Failed to create database && exit /b 1

:createFilegroups

SET STAGE1_FILEGROUP=[PRIMARY]
IF NOT "%STAGE1_FILEGROUP_FILEPATH%"=="" (
  sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -b -d master -i Database\create_filegroup.sql -v STAGING_DATABASE_NAME="%STAGING_DATABASE_NAME%" FILEGROUP_NAME=STAGE1_FILEGROUP FILE_PATH="%STAGE1_FILEGROUP_FILEPATH%" || echo Failed to create filegroup && exit /b 1
  SET STAGE1_FILEGROUP=STAGE1_FILEGROUP
)

:createTables
echo Database %STAGING_DATABASE_NAME% created.

:: SQL\Create\Tables
cd Tables || echo Failed to find directory Tables && exit /b 1

for %%T in (
		TEST.sql
		ROLE.sql
		USER.sql
		EDUCATION_MODULE.sql
		MODULE_DEPENDENCE.sql
		EDUCATION_GROUP.sql
		USER2EDUCATION.sql
		GROUP2MODULE.sql
		GROUP2USER.sql
		MODULE_DOC.sql
		FILTER.sql
		FILTER_FIELD.sql
		DOC2MODULE.sql
		EVENT.sql
		EVENT2USER.sql
            ) do (            
    sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -d %STAGING_DATABASE_NAME% -b -i %%T -v STAGE1_FILEGROUP="%STAGE1_FILEGROUP%" || echo Failed to create table %%T && exit /b 1
    echo %%~nT table created.
)
	

:: SQL\Create\Stored Procedures
cd "..\Stored Procedures" || echo Failed to find Stored Procedures directory && exit /b 1

for %%P in (
			createUserFilter.sql,
			createDocFilter.sql,
			createModuleFilter.sql,
			createGroupFilter.sql,
			createEducationFilter.sql,
			deleteUser.sql,
			deleteEducation.sql
			) do (	
    sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -d %STAGING_DATABASE_NAME% -b -i %%P || echo Failed to create stored procedure %%P && exit /b 1
    echo %%~nP stored procedure created.
)

:: SQL\Create\Functions
cd "..\Functions" || echo Failed to find Functions directory && exit /b 1

for %%P in (
) do (
    sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -d %STAGING_DATABASE_NAME% -b -i %%P  || echo Failed to create function %%P && exit /b 1
    echo %%~nP function created.
)

:: SQL\Create
cd ..

sqlcmd -S %DBMS_SERVER% %CONNECTION_OPTIONS% -d %STAGING_DATABASE_NAME% -b -i User.sql -v STAGING_DATABASE_NAME="%STAGING_DATABASE_NAME%" -v STAGING_DATABASE_USERNAME="%STAGING_DATABASE_USERNAME%" -v STAGING_DATABASE_PASSWORD="%STAGING_DATABASE_PASSWORD%"  || echo Failed to execute User.sql && exit /b 1
echo %STAGING_DATABASE_USERNAME% user created.

echo SUCCESS CREATED ALL.



